package org.caowei.samples.dao;

import java.util.List;

import org.caowei.samples.model.Subject;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository

@Transactional
public class SubjectDAOImpl extends BaseHibernateDAO<Subject> implements ISubjectDAO{

	//获取所有当前科目关联的学生在DAO的查询代码
	@Override
	@SuppressWarnings("unchecked")
	public List<String> getAllUserBySubject(Integer subjectId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :subjectId");
		
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId", StringType.INSTANCE)
				.setInteger("subjectId", subjectId)
				.list();
	}


	
	@Override
	public List<Subject> findAllSubjects() throws Exception{
		return this.loadAll();
		
	}
	
	@Override
	public void createSubject(Subject subject) throws Exception{
		if (subject != null) {
			this.save(subject);
		}
	}
	@Override
	public void editSubject(Subject subject) throws Exception{
		if (subject != null) {
			this.update(subject);
		}
	}
	@Override
	public void deleteSubject(int subId) throws Exception{
		Subject exsitsSubject = this.get(subId);
		if (exsitsSubject != null) {
			this.delete(exsitsSubject);
		}
	}
	
	@Override
	public Subject getSubjectById(int subId) throws Exception{
		return this.get(subId);
		
	}



	@Override
	public void insertSubjectUsers(List<String> insertList, Integer subjectId) {
		String sql = null;
		//添加多条数据
		for(String userId : insertList){
			sql = "insert into exam_user_subject(user_id,sub_id) values(:userId, :subjectId)";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
		
	}



	@Override
	public void deleteSubjectUsers(List<String> delList, Integer subjectId) {
		String sql = null;
		//删除多条数据
		for(String userId : delList){
			sql = "delete exam_user_subject from exam_user_subject where user_id = :userId and sub_id = :subjectId";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
	}

	
}
